package com.cpi.cornpp.futuresversions.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.futuresversions.dto.CornFuturesVersionsDTO;
import com.cpi.cornpp.futuresversions.exception.CornFuturesVersionsException;
import com.cs.common.init.SystemParameterCollection;
import com.cs.common.init.beans.SystemParameterBean;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

/**
 * 
 * @description: This Class is used to handle the Corn Futures Versions Data Access
 * @author Edgar Joao
 */
public class CornFuturesVersionsDAO extends DBDAO{
	/** Datasource name*/
	private static final String DATASOURCE 	= "cornpp";//$NON-NLS-1$
	/** SQL List Corn Futures Versions */ 
	private static final String SQL_LIST_CORN_FUTURES_VERSIONS =  "SELECT corn_future_version_id, corn_future_id, application_future_period_version_id, periodApp.short_name + ' ' + convert( varchar, periodApp.year ) AS application_future_period_version_description, account_future_period_version_id, periodAcc.short_name + ' ' + convert( varchar, periodAcc.year ) AS account_future_period_version_description, reference, quantity, unit_price, unit_price * ( ( quantity / (select qty from units where unit_id = 'CON') )*(select qty from units where unit_id = 'BUS') ) AS price, corn_origin_id FROM corn_futures_versions future LEFT JOIN futures_periods_versions app ON app.future_period_version_id = future.application_future_period_version_id AND app.version_id = ? INNER join futures_periods periodApp ON app.future_period_id = periodApp.future_period_id LEFT JOIN futures_periods_versions acc ON acc.future_period_version_id = future.account_future_period_version_id and acc.version_id = ? INNER JOIN futures_periods periodAcc ON acc.future_period_id = periodAcc.future_period_id";//$NON-NLS-1$
	/** SQL Insert Corn Futures Versions */ 
	private static final String SQL_INSERT_CORN_FUTURES_VERSIONS =  "INSERT INTO corn_futures_versions (application_future_period_version_id, account_future_period_version_id, reference, quantity, unit_price, purchase_date, corn_origin_id) values (?,?,?,?,?,getdate(),?)";//$NON-NLS-1$
	/** SQL Delete Corn Futures Versions */ 
	private static final String SP_DELETE_CORN_FUTURES_VERSIONS =  "exec sp_deleteCornFutureVersion ?,? ";//$NON-NLS-1$
	/** SQL Update Corn Futures Versions */ 
	private static final String SQL_UPDATE_CORN_FUTURES_VERSIONS =  "UPDATE corn_futures_versions SET application_future_period_version_id = ?, account_future_period_version_id = ?, reference = ?, quantity = ?, unit_price = ?, corn_origin_id = ? WHERE corn_future_version_id = ?";//$NON-NLS-1$
	/** SQL Check Id before Insert Corn Futures Versions */ 
	private static final String SQL_BEFORE_INSERT_CORN_FUTURES_VERSIONS =  "";//$NON-NLS-1$
	/** SQL get Corn Futures Versions by Id */ 
	private static final String SQL_GET_CORN_FUTURES_VERSIONS_BY_ID =  "SELECT corn_future_version_id, application_future_period_version_id, account_future_period_version_id, reference, quantity, unit_price, corn_origin_id FROM corn_futures_versions WHERE corn_future_version_id = ?";//$NON-NLS-1$
	/** SQL get List  */ 
	private static final String SQL_GET_CORN_FUTURES_PERIODS_VERSIONS =  "select futures_periods_versions.future_period_version_id, futures_periods.short_name + ' ' + convert( varchar, futures_periods.year) AS short_description from futures_periods_versions inner join futures_periods on futures_periods_versions.future_period_id = futures_periods.future_period_id where version_id = ?";//$NON-NLS-1$
	/** SQL get List*/
	private static final String SQL_CORN_FUTURES_VERSIONS_BY_CORN_FUTURE_ID =  "SELECT corn_future_version_id, corn_future_id, corn_origin_id FROM corn_futures_versions WHERE corn_future_id = ?";//$NON-NLS-1$
	
	/**
	 * This get a List of Corn Futures Versions
	 * @author Edgar Joao	 
	 * @return
	 */
	public List listFuturesVersions(int orderByColumn, boolean ascendingOrder, int versionId) throws CornFuturesVersionsException{
		StringBuffer sql = null;
		ArrayList arrayResult = null;
		PreparedStatement preparedStatement =  null;
		CornFuturesVersionsDTO cornFuturesVersionsDTO = null;
		
		sql = new StringBuffer(SQL_LIST_CORN_FUTURES_VERSIONS);
		sql.append(SQL_ORDER_BY);
		
		switch (orderByColumn){
			case 0: sql.append(CornFuturesVersionsDTO.ACCOUNT_FUTURE_PERIOD_VERSION_ORDER);
					break;
			case 1: sql.append(CornFuturesVersionsDTO.APPLICATION_FUTURE_PERIOD_VERSION_ORDER);
					break;
			case 2: sql.append(CornFuturesVersionsDTO.QUANTITY);
					break;
			case 3: sql.append(CornFuturesVersionsDTO.UNIT_PRICE);
					break;					
			case 4: sql.append(CornFuturesVersionsDTO.PRICE);
					break;
			case 5: sql.append(CornFuturesVersionsDTO.CORN_ORIGIN);
			break;

		}
		if (ascendingOrder){
			sql.append(SQL_ASCENDING_ORDER);
		} else {
			sql.append(SQL_DESCENDING_ORDER);
		}
		try {
			arrayResult = new ArrayList(0);					
			preparedStatement =  prepareSQL(sql.toString(), DATASOURCE);
			preparedStatement.setInt(1, versionId);
			preparedStatement.setInt(2, versionId);
			ResultSet rst = executeQuery(preparedStatement);
			while (rst.next()){
				cornFuturesVersionsDTO = new CornFuturesVersionsDTO();				
				cornFuturesVersionsDTO.setCornFutureVersionId(rst.getInt(1));
				cornFuturesVersionsDTO.setCornFutureId(rst.getInt(2));
				cornFuturesVersionsDTO.setApplicationFuturePeriodVersionId(rst.getInt(3));
				cornFuturesVersionsDTO.setApplicationFuturePeriodVersionDescription(rst.getString(4));
				cornFuturesVersionsDTO.setAccountFuturePeriodVersionId(rst.getInt(5));
				cornFuturesVersionsDTO.setAccountFuturePeriodVersionDescription(rst.getString(6));
				cornFuturesVersionsDTO.setReference(rst.getString(7));
				cornFuturesVersionsDTO.setQuantity(rst.getInt(8));
				cornFuturesVersionsDTO.setUnitPrice(rst.getDouble(9));
				cornFuturesVersionsDTO.setPrice(rst.getDouble(10));
				cornFuturesVersionsDTO.setCornOrigin(rst.getString(11));
				arrayResult.add(cornFuturesVersionsDTO);
			}		
		}catch (PoolException poolException){
			CornFuturesVersionsException cornFuturesVersionsException = null;
			poolException.printStackTrace(System.out);
			cornFuturesVersionsException = new CornFuturesVersionsException (poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw cornFuturesVersionsException;
		}catch (SQLException sqlException){
			CornFuturesVersionsException cornFuturesVersionsException = null;
			sqlException.printStackTrace(System.out);
			cornFuturesVersionsException = new CornFuturesVersionsException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw cornFuturesVersionsException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return arrayResult;
	}
	

	/**
	 * Get a Corn Futures Versions
	 * @author Edgar Joao
	 * @param entity
	 * @return
	 * @throws CornFuturesVersionsException
	 */
	public CornFuturesVersionsDTO getCornFuturesVersions(CornFuturesVersionsDTO entity) throws CornFuturesVersionsException{
		PreparedStatement preparedStatement =  null;
		CornFuturesVersionsDTO cornFuturesVersionsDTO = null;
		try {
			preparedStatement =  prepareSQL(SQL_GET_CORN_FUTURES_VERSIONS_BY_ID, DATASOURCE);						
			preparedStatement.setInt(1, entity.getCornFutureId());						
			
			ResultSet rst = executeQuery(preparedStatement);
			if (rst.next()) {	
				cornFuturesVersionsDTO = new CornFuturesVersionsDTO();				
				cornFuturesVersionsDTO.setCornFutureId(rst.getInt(1));
				cornFuturesVersionsDTO.setApplicationFuturePeriodVersionId(rst.getInt(2));
				cornFuturesVersionsDTO.setAccountFuturePeriodVersionId(rst.getInt(3));				
				cornFuturesVersionsDTO.setReference(rst.getString(4));
				cornFuturesVersionsDTO.setQuantity(rst.getInt(5));
				cornFuturesVersionsDTO.setUnitPrice(rst.getDouble(6));
				cornFuturesVersionsDTO.setCornOrigin(rst.getString(7));
			}
		
		}catch (PoolException poolException){
			CornFuturesVersionsException cornFuturesVersionsException = null;
			poolException.printStackTrace(System.out);
			cornFuturesVersionsException = new CornFuturesVersionsException (poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw cornFuturesVersionsException;
		}catch (SQLException sqlException){
			CornFuturesVersionsException cornFuturesVersionsException = null;
			sqlException.printStackTrace(System.out);
			cornFuturesVersionsException = new CornFuturesVersionsException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw cornFuturesVersionsException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return cornFuturesVersionsDTO;
	}
	
	/**
	 * Get a Corn Futures Versions by CORN_FUTURE_ID
	 * @author Edgar Joao
	 * @param entity
	 * @return
	 * @throws CornFuturesVersionsException
	 */
	public CornFuturesVersionsDTO getCornFuturesVersionsByCornFutureId(int cornFutureId) throws CornFuturesVersionsException{
		PreparedStatement preparedStatement =  null;
		CornFuturesVersionsDTO cornFuturesVersionsDTO = null;
		try {
			preparedStatement =  prepareSQL(SQL_CORN_FUTURES_VERSIONS_BY_CORN_FUTURE_ID, DATASOURCE);						
			preparedStatement.setInt(1,cornFutureId);
			
			ResultSet rst = executeQuery(preparedStatement);
			if (rst.next()) {	
				cornFuturesVersionsDTO = new CornFuturesVersionsDTO();				
				cornFuturesVersionsDTO.setCornFutureVersionId(rst.getInt(1));
				cornFuturesVersionsDTO.setCornFutureId(rst.getInt(2));
				cornFuturesVersionsDTO.setCornOrigin(rst.getString(2));
			}
		
		}catch (PoolException poolException){
			CornFuturesVersionsException cornFuturesVersionsException = null;
			poolException.printStackTrace(System.out);
			cornFuturesVersionsException = new CornFuturesVersionsException (poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw cornFuturesVersionsException;
		}catch (SQLException sqlException){
			CornFuturesVersionsException cornFuturesVersionsException = null;
			sqlException.printStackTrace(System.out);
			cornFuturesVersionsException = new CornFuturesVersionsException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw cornFuturesVersionsException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return cornFuturesVersionsDTO;
	}

	/**
	 * Exist Corn Futures Versions Before Insert
	 * @author Edgar Joao
	 * @param entity
	 * @return
	 * @throws CornFuturesVersionsException
	 */
	public boolean existCornFuturesVersions(CornFuturesVersionsDTO entity) throws CornFuturesVersionsException{
		PreparedStatement preparedStatement =  null;
		boolean result = false;		
		try {
			preparedStatement =  prepareSQL(SQL_BEFORE_INSERT_CORN_FUTURES_VERSIONS, DATASOURCE);						
			//preparedStatement.setString(1, entity.getShortName());						
		//	preparedStatement.setInt(2, entity.getPlantId());
			ResultSet rst = executeQuery(preparedStatement);
			if (rst.next()) {				
				result=true;							
			}else{
				result=false;
			}
					
		}catch (PoolException poolException){
			CornFuturesVersionsException cornFuturesVersionsException = null;
			poolException.printStackTrace(System.out);
			cornFuturesVersionsException = new CornFuturesVersionsException (poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw cornFuturesVersionsException;
		}catch (SQLException sqlException){
			CornFuturesVersionsException cornFuturesVersionsException = null;
			sqlException.printStackTrace(System.out);
			cornFuturesVersionsException = new CornFuturesVersionsException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw cornFuturesVersionsException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return result;
	}
	
	/**
	 * Insert a Corn Futures Versions
	 * @author Edgar Joao
	 * @param entity
	 * @return
	 * @throws CornFuturesVersionsException
	 */
	public boolean insertCornFuturesVersions(CornFuturesVersionsDTO entity) throws CornFuturesVersionsException{
		PreparedStatement preparedStatement =  null;
		boolean result = false;
		int exInsert = 0;
		try {
			preparedStatement =  prepareSQL(SQL_INSERT_CORN_FUTURES_VERSIONS, DATASOURCE);		
			preparedStatement.setInt(1, entity.getApplicationFuturePeriodVersionId());
			preparedStatement.setInt(2, entity.getAccountFuturePeriodVersionId());
			preparedStatement.setString(3, entity.getReference());
			preparedStatement.setInt(4, entity.getQuantity());
			preparedStatement.setDouble(5, entity.getUnitPrice());
			preparedStatement.setString(6, entity.getCornOrigin());
			
			exInsert = executeInsert(preparedStatement);
			if (exInsert <= 0 ) {
				CornFuturesVersionsException cornFuturesVersionsException = null;
				cornFuturesVersionsException = new CornFuturesVersionsException (MSG_NO_INSERT,CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
				result=false;
				throw cornFuturesVersionsException;				
			}
			result=true;
		}catch (PoolException poolException){
			CornFuturesVersionsException cornFuturesVersionsException = null;
			poolException.printStackTrace(System.out);
			cornFuturesVersionsException = new CornFuturesVersionsException (poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
			throw cornFuturesVersionsException;
		}catch (SQLException sqlException){
			CornFuturesVersionsException cornFuturesVersionsException = null;
			sqlException.printStackTrace(System.out);
			cornFuturesVersionsException = new CornFuturesVersionsException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
			throw cornFuturesVersionsException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return result;
	}
	
	/**
	 * Update a Corn Futures Versions
	 * @author Edgar Joao
	 * @param entity
	 * @return
	 * @throws CornFuturesVersionsException
	 */
	public boolean updateCornFuturesVersions(CornFuturesVersionsDTO entity) throws CornFuturesVersionsException{
		PreparedStatement preparedStatement =  null;
		boolean result = false;
		int exDelete = 0;
		try {
			preparedStatement =  prepareSQL(SQL_UPDATE_CORN_FUTURES_VERSIONS, DATASOURCE);			
			preparedStatement.setInt(1, entity.getApplicationFuturePeriodVersionId());
			preparedStatement.setInt(2, entity.getAccountFuturePeriodVersionId());
			preparedStatement.setString(3, entity.getReference());
			preparedStatement.setInt(4, entity.getQuantity());			
			preparedStatement.setDouble(5, entity.getUnitPrice());
			preparedStatement.setString(6, entity.getCornOrigin());
			preparedStatement.setInt(7, entity.getCornFutureId());
			
			exDelete = executeInsert(preparedStatement);
			if (exDelete <= 0 ) {
				CornFuturesVersionsException cornFuturesVersionsException = null;
				cornFuturesVersionsException = new CornFuturesVersionsException (MSG_NO_INSERT,CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
				result=false;
				throw cornFuturesVersionsException;				
			}
			result=true;
		}catch (PoolException poolException){
			CornFuturesVersionsException cornFuturesVersionsException = null;
			poolException.printStackTrace(System.out);
			cornFuturesVersionsException = new CornFuturesVersionsException (poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			throw cornFuturesVersionsException;
		}catch (SQLException sqlException){
			CornFuturesVersionsException cornFuturesVersionsException = null;
			sqlException.printStackTrace(System.out);
			cornFuturesVersionsException = new CornFuturesVersionsException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			throw cornFuturesVersionsException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return result;
	}
	
	/**
	 * 
	 * Delete a Corn Futures Versions
	 * @author Edgar Joao 
	 * @param entity
	 * @return
	 * @throws CornFuturesVersionsException
	 */
	public boolean deleteCornFuturesVersions(int versionId, int cornFutureVersionId) throws CornFuturesVersionsException{
		PreparedStatement preparedStatement =  null;
		boolean result = false;
		SystemParameterCollection systemParameterCollection = null;
		SystemParameterBean systemParameterBean = null;
		try {
			preparedStatement =  prepareSQL(SP_DELETE_CORN_FUTURES_VERSIONS, DATASOURCE);
			preparedStatement.setInt(1, versionId);
			preparedStatement.setInt(2, cornFutureVersionId);
			executeInsert(preparedStatement);
			result=true;
		}catch (PoolException poolException){
			CornFuturesVersionsException cornFuturesVersionsException = null;
			poolException.printStackTrace(System.out);
			cornFuturesVersionsException = new CornFuturesVersionsException (poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
			throw cornFuturesVersionsException;
		}catch (SQLException sqlException){
			CornFuturesVersionsException cornFuturesVersionsException = null;
			systemParameterCollection = SystemParameterCollection.getInstance();
			systemParameterBean = systemParameterCollection.getSystemParameterBean(DBDAO.FOREIGN_KEY_ERROR_CODE);
			if (sqlException.getErrorCode() == systemParameterBean.getIntValue()) {
				cornFuturesVersionsException = new CornFuturesVersionsException(DBDAO.FOREIGN_KEY_ERROR_KEY, CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
			} else {
				cornFuturesVersionsException = new CornFuturesVersionsException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_DELETE);
				sqlException.printStackTrace(System.out);
			}
			throw cornFuturesVersionsException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return result;
	}
	
	/**
	 * List Corn Futures Versions
	 * @return
	 * @throws CornFuturesVersionsException
	 */
	public List listCornFuturesPeriodsVersion(int versionId) throws CornFuturesVersionsException{
		CornFuturesVersionsDTO cornFuturesVersionsDTO = null;
		ArrayList arrayResult = null;		
		PreparedStatement preparedStatement =  null;
		try {
			arrayResult = new ArrayList(0);
			preparedStatement =  prepareSQL(SQL_GET_CORN_FUTURES_PERIODS_VERSIONS, DATASOURCE);
			preparedStatement.setInt(1, versionId);
			ResultSet rst = executeQuery(preparedStatement);
			while (rst.next()){
				cornFuturesVersionsDTO = new CornFuturesVersionsDTO();
				cornFuturesVersionsDTO.setFuturePeriodVersionId(rst.getInt(1));
				cornFuturesVersionsDTO.setShortDescription(rst.getString(2));
				arrayResult.add(cornFuturesVersionsDTO);
			}
			
		}catch (PoolException poolException){
			CornFuturesVersionsException cornFuturesVersionsException = null;
			poolException.printStackTrace(System.out);
			cornFuturesVersionsException = new CornFuturesVersionsException (poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			throw cornFuturesVersionsException;	
		}catch (SQLException sqlException){
			CornFuturesVersionsException cornFuturesVersionsException = null;
			sqlException.printStackTrace(System.out);
			cornFuturesVersionsException = new CornFuturesVersionsException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw cornFuturesVersionsException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return arrayResult;
	}
	
}
